package dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import util.Debug;
/**
 * Singleton for accessing a MySQL database.
 * @author Jolle
 * @version 1.0.0
 */
public class AccessMySQL implements DBAccessInterface {
	
	private static AccessMySQL instance = null;
	
	private Connection conn = null;
	private DSN dsn = null;
	private Statement stmtRS = null;	// used for queried result set
	private String dbName = "";

	/**
	 * Private constructor.
	 */
	private AccessMySQL() {
		
	}
	/**
	 * Return the current instance.
	 * @return instance
	 */
	public static AccessMySQL getInstance() {
		if (instance == null) {
			instance = new AccessMySQL();
		}
		return instance;
	}
	/**
	 * Set the DSN.
	 * @param dsn
	 */
	public void setDSN(DSN dsn) {
		this.dsn = dsn;
	}
	/**
	 * Get the database name.
	 * @return String
	 */
	public String getDbName() {
		return this.dbName;
	}
	/**
	 * Check whether a connection exists.
	 * @return true if a connection is open
	 * @throws DBException
	 */
	public boolean isConnected() throws DBException {
		if (this.conn == null) {
			return false;
		}
		else {
			try {
				return !this.conn.isClosed();
			} catch (Exception e) {
				throw new DBException("Error while checking connection", e);
			}
		}
	}
	/**
	 * Connect to database.
	 * @throws DBException
	 */
	public void connect() throws DBException {
		connect(this.dbName);
	}
	/**
	 * Connect to mySQL.
	 * @param database
	 * @throws DBException
	 */
	public void connect(String database) throws DBException {
		String dsnString = "jdbc:mysql://" + this.dsn.host + ":" + this.dsn.port + "/" + database;
		Debug.print("connecting MYSQL: " + database);
		Debug.print("dsnString: " + dsnString);
		try {
			this.conn = DriverManager.getConnection(dsnString, this.dsn.user, this.dsn.pass);
			this.dbName = database;
		} catch (Exception e) {
			throw new DBException("Error while conecting", e);
		}
	}
	/**
	 * Execute an sql statement without return any value.
	 * @param query
	 * @throws DBException
	 */
	public void execute(String query) throws DBException {
		try {
		    Statement stmt = this.conn.createStatement();
		    stmt.executeUpdate(query);
		    stmt.close();
		} catch (Exception e) {
			throw new DBException("Error while executing command:\n" + query, e);
		}
	}
	/**
	 * Get a result set from a query.
	 * @param query
	 * @return ResultSet
	 * @throws DBException
	 */
	public ResultSet query(String query) throws DBException {
		ResultSet rs = null;
		try {
			this.stmtRS = this.conn.createStatement();
			rs = this.stmtRS.executeQuery(query);
		} catch (Exception e) {
			throw new DBException("Error while executing query:\n" + query, e);
		}
		return rs;
	}
	/**
	 * Close current statement.
	 */
	public void closeStatement() throws DBException {
		if (this.stmtRS == null) {
			return;
		}
		try {
			if (!this.stmtRS.isClosed()) {
				Debug.print("close statement");
				this.stmtRS.close();
			}
		} catch (Exception e) {
			throw new DBException("Error while closing statement", e);
		}
	}
	/**
	 * Close database connection.
	 * @throws DBException
	 */
	public void closeConnection() throws DBException {
		closeStatement();
		if (this.isConnected()) {
			try {
				Debug.print("close connection");
				this.conn.close();
			} catch (Exception e) {
				throw new DBException("Error while closing connection", e);
			}
		}
	}
	/**
	 * Get a list of existing databases in the current connection.
	 * @return String array with database names
	 */
	public String[] getDataBasesList() throws DBException {
		ArrayList<String> dbList = new ArrayList<String>();
		try {
			DatabaseMetaData dbmd = this.conn.getMetaData();
			ResultSet dbs = null;
			dbs = dbmd.getCatalogs();
			while (dbs.next()) {
				dbList.add(dbs.getString(1));
			}
			dbs.close();
		} catch (Exception e) {
			throw new DBException("Error while retrieving databases", e);
		}
		String[] keyArray = dbList.toArray(new String[dbList.size()]);
		Arrays.sort(keyArray);
		return keyArray;
	}
	/**
	 * Get a list of existing tables in the current database.
	 * @return String array with table names
	 */
	public String[] getTablesList() throws DBException {
		ArrayList<String> tableList = new ArrayList<String>();
		try {
			ResultSet rs = this.query("SELECT table_name FROM information_schema.tables " +
					"WHERE table_type = 'base table' AND table_schema = '" + this.dbName + "'");
			while (rs.next()) {
				tableList.add(rs.getString("TABLE_NAME"));
			}
			rs.close();
			this.closeStatement();
		} catch (Exception e) {
			throw new DBException("Error while retrieving tables", e);
		}
		String[] keyArray = tableList.toArray(new String[tableList.size()]);
		Arrays.sort(keyArray);
		return keyArray;
	}
	/**
	 * Get a list of key/value pairs (field name/field value).
	 * @param query
	 * @return ArrayList
	 * @throws DBException
	 */
	public ArrayList<HashMap<String, Object>> getResultAsList(String query) throws DBException {
		ResultSet resultSet = this.query(query);
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		try {
			int columns = resultSet.getMetaData().getColumnCount();
			while (resultSet.next()) {
				HashMap<String, Object> row = new HashMap<String, Object>(columns);
				for (int i = 1; i <= columns; ++i) {
					row.put(resultSet.getMetaData().getColumnName(i), resultSet.getObject(i));
				}
				list.add(row);
			}
			resultSet.close();
		} catch (Exception e) {
			throw new DBException("Error while listing results", e);
		}
		return list;
	}
	/**
	 * Create the given table.
	 * @param name of the table to create
	 * @return SQL statement as string
	 * @throws DBException
	 */
	public void createTable(String table) throws DBException {
		String sql = "";
		if (table == "countrymodel") {
			sql = "CREATE TABLE countrymodel (" +
					"id SMALLINT NOT NULL AUTO_INCREMENT, " +
					"country_id CHAR(2), " +
					"name VARCHAR(50), " +
					"PRIMARY KEY (id)," +
					"KEY (country_id))";
		}
		else if (table == "citymodel") {
			sql = "CREATE TABLE citymodel (" +
					"id SMALLINT NOT NULL AUTO_INCREMENT, " +
					"city_id CHAR(4), " +
					"name VARCHAR(80), " +
					"country_id CHAR(2), " +
					"country_name VARCHAR(50), " +
					"lat DECIMAL(5,2), " +
					"lon DECIMAL(5,2), " +
					"PRIMARY KEY (id), " +
					"KEY (city_id))";
		}
		else if (table == "weathermodel") {
			sql = "CREATE TABLE weathermodel (" +
					"id SMALLINT NOT NULL AUTO_INCREMENT, " +
					"city_id CHAR(4) NOT NULL, " +
					"info VARCHAR(50) NOT NULL, " +
					"temperature DECIMAL(5,0) NOT NULL, " +
					"wind_direction VARCHAR(15) NOT NULL, " +
					"wind_deg SMALLINT NOT NULL, " +
					"wind_speed DECIMAL(5,0) NOT NULL, " +
					"observ_time CHAR(7) NOT NULL, " +
					"tstamp DECIMAL(13,0) NOT NULL, " +
					"date_timestamp TIMESTAMP NOT NULL, " +
					"visibility SMALLINT NOT NULL, " +
					"pressure DECIMAL(6,2) NOT NULL, " +
					"clouds CHAR(9) NOT NULL, " +
					"PRIMARY KEY (id))";
		}
		else {
			return;
		}
		this.execute(sql);
	}
	/**
	 * Check whether the given table exists in current database.
	 * @param table name to check
	 * @return true if table exists, otherwise false
	 * @throws DBException
	 */
	public boolean tableExists(String table) throws DBException {
		String[] tables = this.getTablesList();
		for (String tableName : tables) {
			if (tableName.equalsIgnoreCase(table)) {
				return true;
			}
		}
		return false;
	}
}
